package src.DAO;

import src.jdbc.PostgreSQL;
import src.Entities.Estudio;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;


public class EstudioDAO {
    
    public String deleteEstudio(int estudioId){
        try {
            String sql = "DELETE FROM estudio WHERE estudio_id = ?;";
            PreparedStatement delete = PostgreSQL.getConnection().prepareStatement(sql);
            delete.setInt(1, estudioId);
            return String.valueOf(delete.execute());
        } catch (Exception e) {
            return e.toString();
        }
    }

    public String insertEstudio(Estudio study) {
        try {
            String sql = "INSERT " +
                    "       INTO Estudio(estudio_id, nombre_estudio, institution, tipo_estudio, nivel_estudio, persona_id)" +
                    "     VALUES (NEXTVAL('estudio_seq'), ?, ?,?,?,?,?)";
            PreparedStatement insert = PostgreSQL.getConnection().prepareStatement(sql);
            insert.setInt(1, study.getEstudioId());
            insert.setString(2, study.getNombreEstudio());
            insert.setString(3, study.getInstitucion());
            insert.setString(4, study.getTipoEstudio());
            insert.setString(5, study.getNivelEstudio());
            insert.setInt(6, study.getPersonaId());
            return String.valueOf(insert.execute());
        } catch (Exception e) {
            return e.toString();
        }
    }

    public ArrayList selectEstudio(String sql) {
        ArrayList results = new ArrayList();
        try {
            Connection con = PostgreSQL.getConnection();
            Statement select = con.createStatement();
            ResultSet query = select.executeQuery(sql);
            while (query.next()) {
                Estudio study = new Estudio(query.getInt("estudio_id"),query.getString("nombre_estudio"),
                                        query.getString("institucion"), query.getString("tipo_estudio"), query.getString("nivel_estudio"),
                                        query.getInt("persona_id"));
                results.add(study);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return results;
    }

    public String updateEstudio(Estudio study) {
        try {
            String sql = "UPDATE Estudio" +
                    "        SET nombre_estudio = ?, institucion = ?, tipo_estudio = ?, nivel_estudio = ?, persona_id = ?" +
                    "      WHERE estudio_id = ?";
            PreparedStatement update = PostgreSQL.getConnection().prepareStatement(sql);
            update.setString(1, study.getNombreEstudio());
            update.setString(2, study.getInstitucion());
            update.setString(3, study.getTipoEstudio());
            update.setString(4, study.getNivelEstudio());
            update.setInt(5, study.getPersonaId());
            update.setInt(6, study.getEstudioId());
            return String.valueOf(update.execute());
        } catch (Exception e) {
            return e.toString();
        }
    }
}
